/*
	排序 
	使用 order by子句
	语法结构: 	select 字段名 from 表名 [where 字段名 = 值] order by 字段名称 [ASC/DESC]
		ASC 升序排序 (默认升序)
		DESC 降序排序
*/

-- 单列排序 按照某一个字段进行排序
-- 使用salary 字段 对emp表进行排序
SELECT * FROM emp ORDER BY salary;
SELECT * FROM emp ORDER BY salary DESC;

-- 组合排序 同时对多个字段进行排序
-- 在薪资的排序基础上,再去使用 id字段进行排序
SELECT * FROM emp ORDER BY salary DESC,eid DESC;


-- 组合排序的特点: 如果第一个字段 值相同,就按照第二个字段进行排序.


/*
	聚合函数
		作用:将一列数据作为一个整体,进行纵向的计算的
	
	常用的聚合函数
		count(字段) 统计记录数
		sum(字段) 求和操作
		max(字段) 求最大值
		min(字段) 求最小值
		avg(字段) 求平均值
	
	语法格式
		select 聚合函数(字段名) from 表名 [where 条件]
*/
#1 查询员工的总数
SELECT COUNT(eid) FROM emp;
SELECT COUNT(*) FROM emp;
SELECT COUNT(1) FROM emp;

-- count函数 在统计的时候回忽略空值
-- 注意 不要使用带空值的列 进行 count
SELECT COUNT(dept_name) FROM emp;
#2 查看员工总薪水、最高薪水、最小薪水、薪水的平均值
SELECT SUM(salary),MAX(salary),MIN(salary),AVG(salary) FROM emp;

#3 查询薪水大于4000员工的个数
SELECT COUNT(*) FROM emp WHERE salary>4000;
#4 查询部门为'教学部'的所有员工的个数
SELECT COUNT(*) FROM emp WHERE dept_name='教学部';
#5 查询部门为'市场部'所有员工的平均薪水
SELECT AVG(salary) FROM emp WHERE dept_name='市场部';

/*
	分组查询 使用 group by 子句
	
	语法格式: select 分组字段/聚合函数 from 表名 group by 分组字段
*/


# 通过性别字段 进行分组,求各组的平均薪资
SELECT sex,AVG(salary) FROM emp GROUP BY sex;

#1.查询所有部门信息 
SELECT dept_name AS '部门名称' FROM emp GROUP BY dept_name;
#2.查询每个部门的平均薪资
SELECT dept_name,AVG(salary) FROM emp GROUP BY dept_name;
#3.查询每个部门的平均薪资, 部门名称不能为null
SELECT dept_name,AVG(salary) FROM emp WHERE dept_name IS NOT NULL GROUP BY dept_name;
# 查询平均薪资大于6000的部门.
-- 1. 首先要分组求平均薪资
-- 2. 求出 平均薪资大于 6000的部门

-- 在分组之后 进行条件过滤 使用:  having 判断条件
SELECT dept_name,AVG(salary) FROM emp WHERE dept_name IS NOT NULL 
GROUP BY dept_name HAVING AVG(salary)>6000;

/*
	where 与 having的区别
		where 
			1.在分组前进行过滤
			2.where后面不能跟 聚合函数
		having
			1.是在分组后进行条件过滤
			2.having 后面可以写 聚合函数

*/

/*
	limit 通过limit 去指定要查询的数据的条数 行数
	
	语法格式
		select 字段 from 表名 limit offset, length;
	参数说明:
		offset: 起始行数 默认从0 开始计数
		length: 返回的行数 (要查询几条数据)
*/

# 查询emp表中的前 5条数据
SELECT * FROM emp LIMIT 5;
SELECT * FROM emp LIMIT 0,5;

# 查询emp表中 从第4条开始,查询6条
SELECT * FROM emp LIMIT 3,6;
-- limit 分页操作, 每页显示3条
SELECT * FROM emp LIMIT 0,3;
SELECT * FROM emp LIMIT 3,3;
SELECT * FROM emp LIMIT 6,3;
-- 分页公式 起始行数 = (当前页码 - 1) * 每页显示条数

-- --------------------------------约束--------------------------------------------
/*
	约束
		约束是指对数据进行一定的限制,来保证数据的完整性 有效性 正确性
		
	常见的约束
		主键约束	primary key 
		唯一约束	unique
		非空约束	not null
		外键约束	foreign key
*/

/*
	主键约束
		特点	不可重复 唯一 非空
		作用	用来表示数据库中的每一条记录
		
	语法格式
		字段名	字段类型 primary key
*/

-- 方式1 创建一个带有主键的表
CREATE TABLE emp2(
    eid INT PRIMARY KEY,
    ename VARCHAR(20),
    sex CHAR(1)
);
DESC emp2;
-- 方式2 创建
DROP TABLE emp2;-- 删除表

CREATE TABLE emp2(
    eid INT,
    ename VARCHAR(20),
    sex CHAR(1),
    PRIMARY KEY(eid)-- 指定eid为主键
);

-- 方式3 创建表之后 再添加主键
CREATE TABLE emp2(
    eid INT,
    ename VARCHAR(20),
    sex CHAR(1)
);
-- 通过DDL语句 添加主键约束
ALTER TABLE emp2 ADD PRIMARY KEY(eid);

-- 删除主键 DDL语句
ALTER TABLE emp2 DROP PRIMARY KEY;
/*
	主键的自增
		关键字:  auto_increment 主键的自动增长(字段类型必须是 整数类型)
*/

-- 创建主键自增的表
CREATE TABLE emp2(
    eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(20),
    sex CHAR(1)
);
-- 添加数据 观察主键变化
INSERT INTO emp2(ename,sex) VALUES('张三','男');
INSERT INTO emp2(ename,sex) VALUES('李四','男');
INSERT INTO emp2 VALUES(NULL,'翠花','女');
INSERT INTO emp2 VALUES(NULL,'艳秋','女');
SELECT * FROM emp2;
-- 修改自增起始值 
DROP TABLE emp2;
-- 重新创建自增主键的表, 自定义自增的其实位置
CREATE TABLE emp2(
    eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(20),
    sex CHAR(1)
)AUTO_INCREMENT=100;

/*
	DELETE和TRUNCATE对自增长的影响
	
	delete 删除表中所有数据, 将表中的数据逐条删除.
	truncate 删除表中的所有数据, 是将整个表删除,然后再创建一个结构相同表.
*/

-- delete 方式删除所有数据 
DELETE FROM emp2;-- delete 删除对主键没有影响

INSERT INTO emp2(ename,sex) VALUES('张百万','男'); -- 104
INSERT INTO emp2(ename,sex) VALUES('艳秋','女'); -- 105
-- truncate 删除所有数据
TRUNCATE TABLE emp2; -- 自增从1开始

INSERT INTO emp2(ename,sex) VALUES('张百万','男'); -- 1
INSERT INTO emp2(ename,sex) VALUES('艳秋','女'); -- 2


/*
	非空约束
		特点 表中某一列不予许为空
		
	语法格式
		字段名 字段类型 not null
*/
DROP TABLE emp2;
CREATE TABLE emp2(
    eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(20) NOT NULL,
    sex CHAR(1)
);

/*
	唯一约束
		特点: 表中的某一列不能够重复 (对null值 不做唯一判断)

	语法格式
		字段名 字段类型 unique
*/
-- 创建 emp3表 为ename 字段添加 唯一约束
CREATE TABLE emp3(
    eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(20) UNIQUE,
    sex CHAR(1)
);
-- 测试唯一约束
INSERT INTO emp3 VALUES(1,'张百万','女');
SELECT * FROM emp3;
-- Duplicate entry '张百万' for key 'ename' 不能重复
INSERT INTO emp3 VALUES(2,'张百万','女');

-- 唯一约束的值 可以为 null
INSERT INTO emp3 VALUES(2,NULL,'女');

-- 主键约束和唯一约束的区别
	-- 1.主键约束 他是唯一且不能为空的
	-- 2.唯一约束 唯一,但是可以为空
	-- 3.一个表中只能有一个主键,但是可以有多个唯一约束

-- 外键约束 多表中再学习

/*
	默认值
		特点 用来指定 某一列的默认值
		
	语法格式
		字段名 字段类型 default 默认值
*/
--  创建 emp4表, 指定 sex默认为 女
CREATE TABLE emp4(
    eid INT PRIMARY KEY,
    ename VARCHAR(20) UNIQUE,
    sex CHAR(1) DEFAULT '女'
);
SELECT * FROM emp4;

INSERT INTO emp4(eid,ename) VALUES(1,'杨幂');
INSERT INTO emp4(eid,ename) VALUES(2,'柳岩');
-- 不使用默认值
INSERT INTO emp4 VALUES(3,'蔡徐坤','男');

-- ------------------------事务-----------------------------------------------------------------
-- 创建账户表
CREATE TABLE account(
	-- 主键
	id INT PRIMARY KEY AUTO_INCREMENT,
	-- 姓名
	NAME VARCHAR(10),
	-- 余额
	money DOUBLE
);

-- 添加两个用户
INSERT INTO account (NAME, money) VALUES ('tom', 1000), ('jack', 1000);

SELECT * FROM account;
-- tom账户 -500元
UPDATE account SET money = money-500 WHERE NAME='tom';

出错了

-- jack账户 + 500元
UPDATE account SET money = money+500 WHERE NAME='jack';

/*
	MySql事务操作
		手动提交事务
			1.开启事务 start transaction; 或者 begin;
			2.提交事务 commit;
			3.回滚事务 rollback;
			
		自动提交事务
			MySql默认的提交方式 自动提交事务
			每执行一条DML语句 都是一个单独的事务
			
*/
-- 手动提交事务演示

-- 成功案例
USE db2;
SELECT * FROM account;

START TRANSACTION; -- 开启事务

UPDATE account SET money = money-500 WHERE NAME='tom';
UPDATE account SET money = money+500 WHERE NAME='jack';

COMMIT; -- 提交事务

-- 失败案例 会进行回滚
START TRANSACTION; -- 开启事务

INSERT INTO account VALUES(NULL,'张百万',3000);
INSERT INTO account VALUES(NULL,'有财',3500);

-- 直接关闭窗口 模拟系统崩溃 数据没有发生改变

-- 如果事务中 SQL 语句没有问题，commit 提交事务，会对数据库数据的数据进行改变。 如果事务中 SQL 语句有问题，rollback 回滚事务，会回退到开启事务时的状态。 

-- 自动提交事务 MySQL 默认每一条 DML(增删改)语句都是一个单独的事务

--  登录mysql，查看autocommit状态。
SHOW VARIABLES LIKE  'autocommit';

-- 把 autocommit 改成 off;
SET @@autocommit=off;

-- 执行修改操作

-- 选择数据库
USE db2;

-- 修改数据
UPDATE account SET money = money - 500 WHERE NAME='jack';

-- 手动提交
COMMIT;

/*
	事务的四大特性
		原子性
			每个事务都是一个整体,不可以再拆分,事务中的所有SQL
			要么都执行成功 要么都执行失败
			
		一致性
			事务在执行之前 数据库的状态,与事务执行之后的状态要保持一致
		
		隔离性
			事务与事务之间不应该相互影响,执行时要保证隔离状态
		
		持久性
			一旦事务执行成功,对数据的修改是持久的.		
		
*/

/*
	MySql的隔离级别
			各个事务之间是隔离,相互独立.但是如果多个事务对数据库中的同一批数据
		进行并发访问的时候,就会引发一些问题,可以通过设置不同的隔离级别来解决
		对应的问题
	
	并发访问的问题
		脏读: 一个事务读取到了另一个事务没有提交的数据.
		不可重复读:	一个事务中 两次读取的数据不一致.
		幻读: 一个事务中,一次查询的结果,无法支撑后续的业务操作.
	
	设置隔离级别
		read uncommitted: 读未提交
			可以防止哪些问题: 无
			
		read committed: 读已提交 (Oracle默认 隔离级别)
			可以防止: 脏读
		
		repeatable read : 可重复读	(MySql默认的隔离级别)
			可以防止: 脏读 ,不可重复读
			
		serializable : 串行化
			可以防止: 脏读 ,不可重复读 ,幻读
			
	注意: 隔离级别 从小到大 安全性是越来越高的,但是效率是越来越低的,
	根据不同的情况选择对应的隔离级别
	
*/

	/*
	查看隔离级别
	select @@tx_isolation;
	
	设置隔离级别
	set global transaction isolation level 级别名称;
	read uncommitted 读未提交
	read committed   读已提交
	repeatable read  可重复读
	serializable     串行化

*/

-- 查看隔离级别	MySql默认隔离级别  repeatable read
SELECT @@tx_isolation;

-- 设置隔离级别为 读已提交
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
